package org.hzero.system.sql;

import org.hzero.system.copy.KnifeBeanUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @ClassName: OracleSqlUtil
 * @Description: 执行oracle的语句
 * @author: tianhao.luo@hand-china.com
 * @date: 2020/9/29  10:12
 */
public class KnifeSqlUtil {
    private static final Logger LOGGER = LoggerFactory.getLogger(KnifeSqlUtil.class);


    interface OracleProperties {
        /**
         * ORACLE url
         */
        String ORACLE_URL = "jdbc:oracle:thin:@10.185.62.42:1521:WebCenDB";
        /**
         * ORACLE 驱动
         */
        String ORACLE_DRIVER = "oracle.jdbc.driver.OracleDriver";
        /**
         * ORACLE 用户名
         */
        String ORACLE_USER = "elearning_users";
        /**
         * ORACLE 密码
         */
        String ORA_PASS_WORD = "elearning_users";

    }

    interface MysqlProperties {
        /**
         * url
         */
        String MYSQL_URL = "jdbc:mysql://db.hzero.org:3306?useUnicode=true&characterEncoding=utf-8&useSSL=false&autoReconnect=true&failOverReadOnly=false";
        /**
         * 驱动
         */
        String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
        /**
         * 用户名
         */
        String MYSQL_USER = "root";
        /**
         * 密码
         */
        String MYSQL_PASS_WORD = "sLl2%%2o2O,.r";

    }

    /**
     * 执行jdbc
     * jdbc流程：
     * 1.加载数据库连接驱动
     * 2.获取数据库连接
     * 3.获取连接声明
     * 4.执行数据库对应查询的sql
     * 5.将结果集进行转化
     *
     * @param sql 查询的sql
     * @param c   Class对象
     * @param <T> 泛型
     * @return 查询结果list
     */
    private static <T> List<T> executeQuerySql(String sql, Class<T> c, String driver) {
        Assert.isTrue(!StringUtils.isEmpty(sql),"执行的sql不能为空！");
        // 存放结果集，sql查询存在多条记录，所以使用List对象来存储
        List<T> result = new ArrayList<>();
        // statement声明执行查询的结果对象
        ResultSet rs = null;
        // 数据库连接对象
        Connection conn = null;
        // 连接的声明
        PreparedStatement stmt = null;
        try {
            if (driver.equals(OracleProperties.ORACLE_DRIVER)) {
                LOGGER.info("====================加载驱动{}====================", OracleProperties.ORACLE_DRIVER);
                Class.forName(OracleProperties.ORACLE_DRIVER);
                LOGGER.info("====================获取数据库连接====================");
                conn = DriverManager.getConnection(OracleProperties.ORACLE_URL, OracleProperties.ORACLE_USER, OracleProperties.ORA_PASS_WORD);
                LOGGER.info("====================创建数据库连接声明====================");
                stmt = conn.prepareStatement(sql);
                LOGGER.info("====================执行sql{}====================", sql);
            } else if (driver.equals(MysqlProperties.MYSQL_DRIVER)) {
                LOGGER.info("====================加载驱动{}====================", MysqlProperties.MYSQL_DRIVER);
                Class.forName(MysqlProperties.MYSQL_DRIVER);
                LOGGER.info("====================获取数据库连接====================");
                conn = DriverManager.getConnection(MysqlProperties.MYSQL_URL, MysqlProperties.MYSQL_USER, MysqlProperties.MYSQL_PASS_WORD);
                LOGGER.info("====================创建数据库连接声明====================");
                stmt = conn.prepareStatement(sql);
                LOGGER.info("====================执行sql{}====================", sql);
            }
            Assert.notNull(stmt, driver + "该驱动并不存在");
            rs = stmt.executeQuery();
            while (rs.next()) {
                T obj = null;
                try {
                    obj = c.newInstance();
                } catch (InstantiationException | IllegalAccessException e) {
                    e.printStackTrace();
                }
                // 获取该类的所有属性
                List<Field> allFieldList = new ArrayList<>();
                List<Method> allMethodList = new ArrayList<>();
                KnifeBeanUtil.addAllFields(c,allFieldList);
                addAllMethods(c,allMethodList);
                List<String> sqlColumn = getSqlColumn(sql);

                List<Field> fieldList = allFieldList.stream().filter(item -> !"public static final".equals(Modifier.toString(item.getModifiers()))).collect(Collectors.toList());
                for (Field field : fieldList) {
                    String fType = field.getType().getSimpleName();
                    String fname = field.getName();
                    //将属性名称转化为驼峰规则
                    String camelCaseString = getCamelCaseString(fname);
                    if (sqlColumn.stream().anyMatch(item ->item.trim().equals(camelCaseString))) {
                        Object value = null;
                        if ("string".equalsIgnoreCase(fType)) {
                            value = rs.getString(camelCaseString);
                        } else if ("int".equalsIgnoreCase(fType) || "integer".equalsIgnoreCase(fType)) {
                            value = rs.getInt(camelCaseString);
                        } else if ("float".equalsIgnoreCase(fType)) {
                            value = rs.getFloat(camelCaseString);
                        } else if ("double".equalsIgnoreCase(fType)) {
                            value = rs.getDouble(camelCaseString);
                        } else if ("boolean".equalsIgnoreCase(fType)) {
                            value = rs.getBoolean(camelCaseString);
                        } else if ("date".equalsIgnoreCase(fType)) {
                            value = rs.getTimestamp(camelCaseString);
                        } else if ("long".equalsIgnoreCase(fType)){
                            value = rs.getLong(camelCaseString);
                        }

                        //使用setXXX方法

                        fname = fname.substring(0, 1).toUpperCase() + fname.substring(1);
                        String mName = "set" + fname;
                        Method m = null;
                        try {
                            m = allMethodList.stream().filter(item -> item.getName().equals(mName)).collect(Collectors.toList()).get(0);
                            m.setAccessible(true);
                            m.invoke(obj, value);
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }

                }
                //将封装好的数据依次加入result
                result.add(obj);

            }

            for (Object o : result) {
                o.toString();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }


    public static <T> List<T> executeOracleQuery(String sql, Class<T> c) {
        return executeQuerySql(sql, c, OracleProperties.ORACLE_DRIVER);
    }

    public static <T> List<T> executeMysqlQuery(String sql, Class<T> c) {
        return executeQuerySql(sql, c, MysqlProperties.MYSQL_DRIVER);
    }

    private static String getCamelCaseString(String javaFieldName){
        char[] chars = javaFieldName.toCharArray();
        StringBuilder sb = new StringBuilder();
        boolean flag = false;
        final char[] big={'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'};
        for (char aChar : chars) {
            for (char c : big) {
                if (c==aChar){
                    sb.append("_").append(String.valueOf(c).toLowerCase());
                    flag = true;
                    break;
                }else {
                    flag = false;
                }
            }
            if (!flag){
                sb.append(aChar);
            }
        }
        return sb.toString();
    }

    /**
     * 获取当前类的所有属性
     *
     * @param aClass    类
     * @param methodList 空的属性list
     */
    public static void addAllMethods(Class<?> aClass, List<Method> methodList) {
        Class<?> superclass = aClass.getSuperclass();
        if (superclass != null) {
            addAllMethods(superclass, methodList);
        }
        methodList.addAll(Arrays.asList(aClass.getDeclaredMethods()));
    }

    public static List<String> getSqlColumn(String sql){
        String s1 = sql.toLowerCase().replaceAll("from\\s*\\S*\\w*\\s*\\S*", "").replaceAll("select","").replaceAll("\\s","");
        String[] split = s1.split(",");
        return Arrays.asList(split);
    }
}
